<?php

class MysqlDB{
	// Conexion Base de datos
	private $conn;
	// Host de la BBDD
	private $host;
	// Puertos de la BBDD
	private $port;
	// Usuario de la BBDD
	private $user;
	// Password del usaurio
	private $password;
	// Base de datos que vamos a acceder
	private $database;
	// Definicion autocommit
	private $autocommit;
	
	// Error producido manejador base de datos
	private $errors = array();
	
	private $persistant;
	private $query;
	private $params;
	private $charset;
	private $result;
	private $arrayResult;
	private $affected;
	private $records;
	
	public function __construct($host, $port, $password, $user, $database, $conn=null, $autocommit = true, $persistant = false, $charset = null){
		if (isset($conn) && is_resource($conn)){
			// Mantenemos la conexion que nos han pasado
			$this->conn = $conn;
		}else{
			// Guardamos los datos para generar la conexion
			$this->host = $host;
			if (isset($port)){
				$this->port = $port;
			}
			$this->user = $user;
			$this->password = $password;
			$this->database = $database;
			$this->autocommit = $autocommit;
			$this->persistant = $persistant;
			$this->charset = $charset;
		}
	}
	
	public function __destruct(){
		$this->closeConnection();
	}
	
	public function createConnection($reuse){
		if (!$this->checkConnection()){
			if (!$this->persistant){
				$this->conn = mysql_connect ($host, $user, $password, true, MYSQL_CLIENT_COMPRESS);
			}else{
				$this->conn = mysql_pconnect ($host, $user, $password, true, MYSQL_CLIENT_COMPRESS);
			}
			if(!$this->conn){
	   			$this->errors[] = 'Error conexion base de datos: ' . mysql_error($this->conn);
				return false;
			}
			if (!$this->selectDB()){
				return false;
			}
			if (isset($this->charset) && $this->charset!=null){
				$this->setCharset();
			}
		}
	}
	
	public function closeConnection(){
		if($this->conn){
			if (!$this->autocommit){
				$this->commit();
			}
			mysql_close($this->conn);
		}
	}
	
public function insert($table, $vars, $exclude = '', $datatypes){
        // Catch Exclusions
        if($exclude == ''){
            $exclude = array();
        }
        array_push($exclude, 'MAX_FILE_SIZE'); // Automatically exclude this one
        // Prepare Variables
        $vars = $this->SecureData($vars, $datatypes);
        $query = "INSERT INTO `{$table}` SET ";
        foreach($vars as $key=>$value){
            if(in_array($key, $exclude)){
                continue;
            }
            $query .= "`{$key}` = '{$value}', ";
        }
        $query = trim($query, ', ');
        return $this->executeSQL($query);
    }
    // Deletes a record from the database
    public function delete($table, $where='', $limit='', $like=false, $wheretypes){
        $query = "DELETE FROM `{$table}` WHERE ";
        if(is_array($where) && $where != ''){
            // Prepare Variables
            $where = $this->SecureData($where, $wheretypes);
            foreach($where as $key=>$value){
                if($like){
                    $query .= "`{$key}` LIKE '%{$value}%' AND ";
                }else{
                    $query .= "`{$key}` = '{$value}' AND ";
                }
            }
            $query = substr($query, 0, -5);
        }
        if($limit != ''){
            $query .= ' LIMIT ' . $limit;
        }
        return $this->executeSQL($query);
    }
    // Gets a single row from $from where $where is true
    public function select($from, $where='', $orderBy='', $limit='', $like=false, $operand='AND',$cols='*', $wheretypes){
        // Catch Exceptions
        if(trim($from) == ''){
            return false;
        }
        $query = "SELECT {$cols} FROM `{$from}` WHERE ";
        if(is_array($where) && $where != ''){
            // Prepare Variables
            $where = $this->SecureData($where, $wheretypes);
            foreach($where as $key=>$value){
                if($like){
                    $query .= "`{$key}` LIKE '%{$value}%' {$operand} ";
                }else{
                    $query .= "`{$key}` = '{$value}' {$operand} ";
                }
            }
            $query = substr($query, 0, -(strlen($operand)+2));
        }else{
            $query = substr($query, 0, -6);
        }
        if($orderBy != ''){
            $query .= ' ORDER BY ' . $orderBy;
        }
        if($limit != ''){
            $query .= ' LIMIT ' . $limit;
        }
        return $this->executeSQL($query);
    }
    // Updates a record in the database based on WHERE
    public function update($table, $set, $where, $exclude = '', $datatypes, $wheretypes){
        // Catch Exceptions
        if(trim($table) == '' || !is_array($set) || !is_array($where)){
            return false;
        }
        if($exclude == ''){
            $exclude = array();
        }
        array_push($exclude, 'MAX_FILE_SIZE'); // Automatically exclude this one
        $set 	= $this->SecureData($set, $datatypes);
        $where 	= $this->SecureData($where,$wheretypes);
        // SET
        $query = "UPDATE `{$table}` SET ";
        foreach($set as $key=>$value){
            if(in_array($key, $exclude)){
                continue;
            }
            $query .= "`{$key}` = '{$value}', ";
        }
        $query = substr($query, 0, -2);
        // WHERE
        $query .= ' WHERE ';
        foreach($where as $key=>$value){
            $query .= "`{$key}` = '{$value}' AND ";
        }
        $query = substr($query, 0, -5);
        return $this->executeSQL($query);
    }
	
	public function selectPS(){
	
	}
	
	public function insertPS(){
	
	}
	
	public function updatePS(){
	
	}
	
	public function deletePS(){
	
	}
	
	public function executeSQL($query){
		$this->lastQuery = $query;
		if($this->result = mysql_query($query, $this->conn)){
			if (gettype($this->result) === 'resource') {
				$this->records  = @mysql_num_rows($this->result);
				$this->affected = @mysql_affected_rows($this->conn);
			} else {
				$this->records  = 0;
				$this->affected = 0;
			}
			if($this->records > 0){
				$this->arrayResults();
				return $this->arrayResult;
			}else{
				return true;
			}
		}else{
			$this->errors[] = mysql_error($this->conn);
			return false;
		}
	}
	
	/**
	 * Metodo que lee la zona horaria establecida en la base de datos.
	 * Coge por defecto la del sistema en caso de que la zona horaria de la dB sea la establecida por el sistema.
	 *
	 * @return string La zona horaria de la base de datos.
	 *
	 */
	public function getTimeZone(){
		// por defecto cogemos la del sistema
		$time_zone = date_default_timezone_get();
	
		$query = "SELECT @@global.time_zone AS time";
	
		if($this->ejecutarSelect($query)){
				
			$reg = $this->fetch_assoc();
				
			if($reg['time'] != "SYSTEM"){
	
				$time_zone = $reg['time'];
				$this->debugMuestraMensaje(__METHOD__,"Zona horaria leida: $time_zone",3);
			}
		}
	
		return $time_zone;
	}
	
	/**
	 * comprobarConexion()
	 *
	 * Comprueba si el objeto tiene una conexion con la base de datos abierta
	 *
	 * @return OK si todo es correcto, NOK en otro caso.
	 */
	public function checkConnection(){
		if (isset($this->conn) && mysql_ping($this->conn)){
			return true;
		}else{
			return false;
		}
	} // Fin de comprobarConexion
	
	public function commit(){
		return mysql_query("COMMIT", $this->conn);
	}
	
	public function rollback(){
		return mysql_query("ROLLBACK", $this->conn);
	}
	public function setCharset( $charset = 'UTF8' ) {
		return mysql_set_charset ( $this->SecureData($charset,'string'), $this->conn);
	}
	public function lastInsertID(){
		return mysql_insert_id($this->databaseLink);
	}
	public function countRows($from, $where=''){
		$result = $this->select($from, $where, '', '', false, 'AND','count(*)');
		return $result["count(*)"];
	}
	public function arrayResult(){
		$this->arrayResult = mysql_fetch_assoc($this->result) or die (mysql_error($this->conn));
		return $this->arrayResult;
	}
	
	public function arrayResults(){
		if($this->records == 1){
			return $this->arrayResult();
		}
		$this->arrayResult = array();
		while ($data = mysql_fetch_assoc($this->result)){
			$this->arrayResult[] = $data;
		}
		return $this->arrayResult;
	}
	
	public function arrayResultsWithKey($key='id'){
		if(isset($this->arrayResult)){
			unset($this->arrayResult);
		}
		$this->arrayResult = array();
		while($row = mysql_fetch_assoc($this->result)){
			foreach($row as $theKey => $theValue){
				$this->arrayResult[$row[$key]][$theKey] = $theValue;
			}
		}
		return $this->arrayResult;
	}
	
	/********************************************************************
	                    Funciones privadas base de datos
	*********************************************************************/
	
	
	private function selectDB(){
		if(!mysql_select_db($this->database, $this->conn)){
			$this->errors = 'Error seleccion base de datos: ' . mysql_error($this->databaseLink);
			return false;
		}else{
			return true;
		}
	}
	
	private function SecureData($data, $types){
		if(is_array($data)){
			$i = 0;
			foreach($data as $key=>$val){
				if(!is_array($data[$key])){
					$data[$key] = $this->CleanData($data[$key], $types[$i]);
					$data[$key] = mysql_real_escape_string($data[$key], $this->conn);
					$i++;
				}
			}
		}else{
			$data = $this->CleanData($data, $types);
			$data = mysql_real_escape_string($data, $this->conn);
		}
		return $data;
	}
	
	private function CleanData($data, $type = ''){
		switch($type) {
			case 'none':
				// useless do not reaffect just do nothing
				//$data = $data;
				break;
			case 'str':
			case 'string':
				settype( $data, 'string');
				break;
			case 'int':
			case 'integer':
				settype( $data, 'integer');
				break;
			case 'float':
				settype( $data, 'float');
				break;
			case 'bool':
			case 'boolean':
				settype( $data, 'boolean');
				break;
				// Y-m-d H:i:s
				// 2014-01-01 12:30:30
			case 'datetime':
				$data = trim( $data );
				$data = preg_replace('/[^\d\-: ]/i', '', $data);
				preg_match( '/^([\d]{4}-[\d]{2}-[\d]{2} [\d]{2}:[\d]{2}:[\d]{2})$/', $data, $matches );
				$data = $matches[1];
				break;
			case 'ts2dt':
				settype( $data, 'integer');
				$data = date('Y-m-d H:i:s', $data);
				break;
				// bonus types
			case 'hexcolor':
				preg_match( '/(#[0-9abcdef]{6})/i', $data, $matches );
				$data = $matches[1];
				break;
			case 'email':
				$data = filter_var($data, FILTER_VALIDATE_EMAIL);
				break;
			default:
				$data = '';
				break;
		}
		return $data;
	}
	
}
?>